﻿-- =============================================
-- Author:		GSJ
-- Create date: 2014-12-30
-- Description:	按银行分类统计实有银行,昨日银行和银行对冲单列
-- =============================================
CREATE PROCEDURE [dbo].[proc_Statistics_Financial_SYYH]
	(
		@CompanyId int,
		@date nchar(10)
	)
AS
BEGIN
	Declare @sRq Datetime,@eRq Datetime,@sRq_o Datetime,@eRq_o Datetime
Set @sRq = Convert(Datetime, @date + ' 00:00:00')
Set @eRq = Convert(Datetime, @date + ' 23:59:59')
--set @CompanyId=50
Set @sRq_o = DATEADD(dd,-1,@sRq)
Set @eRq_o = DATEADD(dd,-1,@eRq)

--;WITH yhsr as(
----银行收入
-- SELECT SUM([Income]) as je,BankId FROM [Finance_RunningAccount] WHERE CompanyId=@CompanyId  AND createdate>=@sRq and createdate<=@eRq 
-- AND (PaymentTypeId=3 or BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 and Companyid= @CompanyId))
-- group by  BankId
-- ),
-- yhzc as (
-- --银行支出
-- SELECT sum(outlayprice) as je,BankId
--  FROM [Finance_Outlay] 
--WHERE  companyidcur=@CompanyId and [cb_flag]=0 and createdate>=@sRq and createdate<=@eRq   AND (PaymentTypeId=3 or BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 and Companyid= @CompanyId)) 
--  group by BankId
-- ),
---- zccb as(
---- --不算在支出中的支出审批成本
---- select sum(je) as je,bankid from (
---- SELECT costprice as je,(Select BankId From [Order] Where OId=p.OrderId) as bankid FROM Project p,[order] as o
----  WHERE  p.CompanyId=@CompanyId AND p.FinanceAudit=2 and o.companyid=@CompanyId and o.oid=p.orderid AND p.FinanceAuditDate>=@sRq and p.FinanceAuditDate<= @eRq
---- AND (o.PaymentTypeId=3 
----   OR o.BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 and companyid=@CompanyId) )
----) as bn1
----group by bankid
----),
--cx as(
----存现
-- SELECT sum(cx) as je,cx_BankId as BankId  FROM txcx t WHERE CompanyId=@CompanyId AND createdate>=@sRq and createdate<=@eRq  AND  (t.cx_PaymentTypeId=3 or t.cx_BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 and Companyid= @CompanyId))
-- group by cx_BankId
-- ),
-- tx as(
-- --提现
-- SELECT sum(tx) as je ,tx_BankId as BankId FROM txcx t WHERE CompanyId=@CompanyId AND createdate>=@sRq and createdate<=@eRq  AND  (t.tx_PaymentTypeId=3 or t.tx_BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 and Companyid= @CompanyId))
-- group by tx_BankId
-- ),
--bank as (
 
-- select fb.id,fb.openbank,
-- isnull((select je from yhsr where yhsr.bankid=fb.id),0) -
--  isnull((select je from yhzc where yhzc.bankid=fb.id),0) +
--  --isnull((select je from zccb where zccb.bankid=fb.id),0) +
--  isnull((select je from cx where cx.bankid=fb.id),0)     -
--  isnull((select je from tx where tx.bankid=fb.id),0) as hjje,0 as zrxj,0  as xjdc
  
  
-- from Finance_Bank as fb where companyid=@CompanyId and PaymentTypeId =2
-- union 
-- select 3,'支票',isnull((select je from yhsr where yhsr.bankid=3),0) -
--  isnull((select je from yhzc where yhzc.bankid=3),0) +
--  --isnull((select je from zccb where zccb.bankid=3),0) +
--  isnull((select je from cx where cx.bankid=3),0)     -
--  isnull((select je from tx where tx.bankid=3),0) as hjje,
-- isnull((select IBB FROM Statistics_Financial  WHERE  CompanyId=@CompanyId AND 
--  createdate>=dateadd(d,-1,@sRq) and createdate<=dateadd(d,-1,@eRq)),0) as zrxj,
--   isnull((SELECT sum(dch_yy) FROM Order_Change_Hedge WHERE CompanyId=@CompanyId AND UpTime>=@sRq and UpTime<=@sRq ),0) as xjdc
--),
;WITH bank as(
select * from bankye where companyid=@CompanyId and flag=2 and [date] between @sRq and @eRq
),
 zrbank as (
 select * from bankye where companyid=@CompanyId and flag=2 and [date] between @sRq_o and @eRq_o
 )

select *,(select sum(drje) from bank) as yhhj,--当日发生额合计
 isnull((select ye from zrbank where zrbank.bankid=bank.bankid),0) as zrye,--昨日余额
  isnull((select sum(ye) from bank ),0) as yehj,  --当日余额合计
  isnull((select sum(ye) from zrbank ),0) as zryehj--昨日余额合计
  from bank 

order by id




END
